SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




ALTER       View v_payment_relationbill
as
select m.bid,m.bno, 
case
  when m.relationbtype=140 then pp.bcode 
  when m.relationbtype=240 then ss.bcode
  when m.relationbtype=110 then po.bcode
  when m.relationbtype=210 then so.bcode
  when m.relationbtype=1140 then ce.bcode
  when m.relationbtype=141 then tt.bcode 
  else app.bcode
end as bcode,
case
  when m.relationbtype=140 then pp.bdate
  when m.relationbtype=240 then ss.bdate
  when m.relationbtype=110 then po.bdate
  when m.relationbtype=210 then so.bdate
  when m.relationbtype=1140 then ce.bdate
  when m.relationbtype=141 then tt.bdate
  else app.bdate
end as bdate,
case
  when m.relationbtype=140 then pp.amount
  when m.relationbtype=240 then -ss.amount
  when m.relationbtype=110 then po.amount
  when m.relationbtype=210 then -so.amount
  when m.relationbtype=1140 then ce.amount
  when m.relationbtype=141 then tt.amount
  else app.amount
end as total,
relationamt =
case b.bstate
when 2 then 
  case
    when m.relationbtype=140 then pp.relationamt
    when m.relationbtype=240 then -ss.relationamt
    when m.relationbtype=110 then 0
    when m.relationbtype=210 then 0
    when m.relationbtype=1140 then ce.relationamt
    when m.relationbtype=141 then tt.relationamt
    else app.relationamt
  end
else
  case
    when m.relationbtype=140 then pp.relationamt-m.amount
    when m.relationbtype=240 then (-ss.relationamt)-m.amount
    when m.relationbtype=110 then 0
    when m.relationbtype=210 then 0
    when m.relationbtype=1140 then ce.relationamt-m.amount
    when m.relationbtype=141 then tt.relationamt-m.amount
    else app.relationamt-m.amount
  end
end, 
needbalamt=
case b.bstate
when 2  then 
  case
    when m.relationbtype=140 then pp.amount-pp.relationamt
    when m.relationbtype=240 then -(ss.amount-ss.relationamt)
    when m.relationbtype=110 then 0
    when m.relationbtype=210 then 0
    when m.relationbtype=1140 then ce.amount-ce.relationamt
    when m.relationbtype=141 then tt.amount-tt.relationamt
    else app.amount-app.relationamt
  end
else 
  case
    when m.relationbtype=140 then pp.amount-pp.relationamt+ m.amount
    when m.relationbtype=240 then -(ss.amount-ss.relationamt-m.amount)
    when m.relationbtype=110 then 0
    when m.relationbtype=210 then 0
    when m.relationbtype=1140 then ce.amount-ce.relationamt+ m.amount
    when m.relationbtype=141 then tt.amount-tt.relationamt+ m.amount
    else app.amount-app.relationamt+m.amount
  end
end,
case
  when m.relationbtype=140 then pp.dcamount
  when m.relationbtype=240 then -ss.dcamount
  when m.relationbtype=110 then po.dcamount
  when m.relationbtype=210 then -so.dcamount
  when m.relationbtype=1140 then ce.dcamount
  when m.relationbtype=141 then tt.dcamount
  else app.amount
end as dctotal,
dcrelationamt =
case b.bstate
when 2 then 
  case
    when m.relationbtype=140 then pp.dcrelationamt
    when m.relationbtype=240 then -ss.dcrelationamt
    when m.relationbtype=110 then 0
    when m.relationbtype=210 then 0
    when m.relationbtype=1140 then ce.dcrelationamt
    when m.relationbtype=141 then tt.dcrelationamt
    else app.dcrelationamt
  end
else
  case
    when m.relationbtype=140 then pp.dcrelationamt-m.dcamount
    when m.relationbtype=240 then (-ss.dcrelationamt)-m.dcamount
    when m.relationbtype=110 then 0
    when m.relationbtype=210 then 0
    when m.relationbtype=1140 then ce.dcrelationamt-m.dcamount
    when m.relationbtype=141 then tt.dcrelationamt-m.dcamount
    else app.dcrelationamt-m.dcamount
  end
end, 
dcneedbalamt=
case b.bstate
when 2  then 
  case
    when m.relationbtype=140 then pp.dcamount-pp.dcrelationamt
    when m.relationbtype=240 then -(ss.dcamount-ss.dcrelationamt)
    when m.relationbtype=110 then 0
    when m.relationbtype=210 then 0
    when m.relationbtype=1140 then ce.dcamount-ce.dcrelationamt 
    when m.relationbtype=141 then tt.dcamount-tt.dcrelationamt
    else app.dcamount-app.dcrelationamt
  end
else 
  case
    when m.relationbtype=140 then pp.dcamount-pp.dcrelationamt+ m.dcamount
    when m.relationbtype=240 then -(ss.dcamount-ss.dcrelationamt-m.dcamount)
    when m.relationbtype=110 then 0
    when m.relationbtype=210 then 0
    when m.relationbtype=1140 then ce.dcamount-ce.dcrelationamt+m.dcamount
    when m.relationbtype=141 then tt.dcamount-tt.dcrelationamt+ m.dcamount
    else app.dcamount-app.dcrelationamt+m.dcamount
  end
end

from tb_paymentdtl m
left join tb_payment b on m.bid=b.bid
left join tb_purchase pp on (m.relationbid=pp.bid and m.relationbtype=140)
left join tb_purexpend app on (m.relationbid=app.bid and m.relationbtype=150)
left join tb_sale ss on (m.relationbid=ss.bid  and m.relationbtype=240)
left join tb_purorder po on (m.relationbid=po.bid and m.relationbtype=110)
left join tb_saleorder so on (m.relationbid=so.bid and m.relationbtype=210)
left join tb_ComExpenditure ce on(m.relationbid=ce.bid and m.relationbtype=1140)
left join (select s.bcode,s.bdate,t.* from tb_thirdtrade t,tb_purchase s where t.btype=141 and t.bid=s.bid) tt on m.relationbid=tt.bid and m.relationbtype=141



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

